package cn.wzvtc.miniserver;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * DAO层，使用JDBC实现增删改查
 * @author Shiwei Xu
 * @date 2020/3/3
 */
public class UserDao {

  private static final String URL = "jdbc:mysql://localhost:3306/sys?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false";
  private static final String USER = "root";
  private static final String PASSWORD = "root";

  /**
   * 数据库连接
   * 初始化时获得一个连接，并一直保持着
   */
  private Connection conn;

  /**
   * DAO的构造方法
   */
  public UserDao() throws Exception {
    // 1.加载驱动程序
    Class.forName("com.mysql.cj.jdbc.Driver");
    // 2.获得数据库连接
    this.conn = DriverManager.getConnection(URL, USER, PASSWORD);
  }

  public List<User> findAll() {
    try {
      Statement stmt = conn.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM sys_user");

      List<User> resultList = new ArrayList<>();
      while (rs.next()) {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setPassword(rs.getString("password"));
        user.setMobile(rs.getString("mobile"));
        resultList.add(user);
      }
      return resultList;
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  public User save(User user) {
    String sql = "INSERT INTO sys_user(name, password, mobile) values(?,?,?)";
    // 预编译SQL，减少sql执行
    try {
      PreparedStatement ptmt = conn.prepareStatement(sql);

      ptmt.setString(1, user.getName());
      ptmt.setString(2, user.getPassword());
      ptmt.setString(3, user.getMobile());

      ptmt.execute();
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }
}
